if exists (select 1
          from sysobjects
          where  id = object_id('bd_kq_step')
          and type in ('P','PC'))
DROP PROCEDURE bd_kq_step
GO
CREATE  PROCEDURE bd_kq_step
	@orderId VARCHAR(max),
	@operate VARCHAR(max)
AS
BEGIN

--创建
CREATE TABLE #data(
		approveOrder VARCHAR (max) COLLATE Chinese_PRC_CI_AS,
		nodeName VARCHAR (max) COLLATE Chinese_PRC_CI_AS,
		userName VARCHAR (max) COLLATE Chinese_PRC_CI_AS,
		status VARCHAR (max) COLLATE Chinese_PRC_CI_AS,
		createTime VARCHAR (max) COLLATE Chinese_PRC_CI_AS
);

insert into #data(approveOrder)
select a.approve_order from bd_kq_approvalRecord a
LEFT JOIN dt_user b on a.handler_id = b.user_serial where order_id = @orderId and a.approve_key like @operate+'%'  GROUP BY a.approve_order;

	DECLARE
		@id VARCHAR (max),
		@approveOrder VARCHAR (max),
		@nodeName VARCHAR (max),
		@userName VARCHAR (max),
		@status VARCHAR (max),
		@createTime VARCHAR (max)
	DECLARE
		cur1 CURSOR FOR SELECT
		a.id,a.approve_order
	FROM bd_kq_approvalRecord a
LEFT JOIN dt_user b on a.handler_id = b.user_serial where order_id = @orderId
		 --声明游标变量
		OPEN cur1 --打开游标
		FETCH cur1 INTO @id,@approveOrder --将游标里的ID,approveOrder赋给变量
	WHILE( @@fetch_status = 0 ) ---@@fetch_status 指针：0   FETCH   语句成功;1   FETCH   语句失败或此行不在结果集中;2   被提取的行不存在。
		BEGIN
			--判断
			select @nodeName = a.node_name,@userName = b.user_lname,@status = a.status,@createTime = CONVERT(VARCHAR(20),a.create_time, 120)
			FROM bd_kq_approvalRecord a
LEFT JOIN dt_user b on a.handler_id = b.user_serial where order_id = @orderId and approve_key like @operate+'%' and
				id = @id;

			--审批人添加,状态判断,时间
			select
				@userName = case when isnull(userName,'')!='' then isnull(userName,'')+','+@userName else @userName end,
				@status = case when (isnull(status,'')!='' and isnull(status,'')=0)  then isnull(status,'') else @status end,
				@createTime = case when isnull(status,'')!='' and isnull(status,'')=0 then createTime else @createTime end
			from #data where approveOrder = @approveOrder;

			UPDATE #data set nodeName = @nodeName,userName = @userName,status = @status,createTime = @createTime where approveOrder = @approveOrder;

			FETCH cur1 INTO @id,@approveOrder --下一个

	END CLOSE cur1 --关闭游标
	DEALLOCATE cur1 --释放游标引用

	--select approveOrder,nodeName+':'+userName as userName,status,createTime from #data
	 IF ((select count(1) from bd_kq_approvalRecord where order_id = @orderId)>=1)
	select step,a.stepName as stepName,isnull(b.userName,'') as userName,isnull(b.status,3) as status,isnull(b.createTime,'') as createTime from
	(
		  select 0 as step, '开始' as stepName
		   union all
		   select
			a.approve_order as step,
			b.name as stepName
		   from bd_kq_approve_detail a
		   LEFT JOIN bd_sys_item b on a.group_id = b.id
		   where approve_key = (select approve_key from bd_kq_approvalRecord where order_id = @orderId and approve_key like @operate+'%' GROUP BY approve_key)
		   union all
		  select 9999 as step, '结束' as stepName
	  ) a
	 LEFT JOIN
	 (select approveOrder,nodeName+':'+userName as userName,status,createTime from #data) b on a.step = b.approveOrder;

--  清除临时表
 	drop table #data;

END
